using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
using ReflectionStudio.Core.Events;
using System;
namespace WPFExcelReport
{
    public partial class VWTSCHUSS
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "TST":
                case "UBT":
                case "SXN":
                default:
                    #region=========
                    dwReturn["WTSMANDANT"] = "00";
                    dwReturn["WTSSFLGNR"] = "D5             ";
                    dwReturn["WTSSTAKZ"] = " ";
                    dwReturn["WTSBEZ"] = "D5W                 ";
                    dwReturn["WTSCADKEY1"] = "             ";
                    dwReturn["WTSCADKEY2"] = "      ";
                    dwReturn["WTSMUSTER"] = "N";
                    dwReturn["WTSSRAPP"] = "16";
                    dwReturn["WTSGRAPP"] = "0";
                    dwReturn["WTSJAC"] = " ";
                    dwReturn["WTSPATRONE"] = "               ";
                    dwReturn["WTSREGU"] = "          ";
                    dwReturn["WTSANZFARB"] = "3";
                    dwReturn["WTSANZMFS"] = "0";
                    dwReturn["WTSGRDSCHT"] = "0";
                    dwReturn["WTSFOLGE"] = "System.Byte[]";
                    dwReturn["WTSSCHUSS01"] = "System.Byte[]";
                    dwReturn["WTSSCHUSS02"] = "System.Byte[]";
                    dwReturn["WTSFAKTOR"] = "0";
                    dwReturn["WTSFILLER"] = "                                                                                           ";
                    dwReturn["WTSAENDZL"] = "4";
                    dwReturn["WTSAENDBEN"] = "INTEXE  ";
                    dwReturn["WTSAENDDAT"] = "20151031";
                    dwReturn["WTSNEUBEN"] = "INTEXE  ";
                    dwReturn["WTSNEUDAT"] = "20151031";
                    #endregion
                    break;
                case "JLT":
                    break;
            }
            //
        }
        public void MapWTSCHUSS(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveWTSCHUSS(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            try
            {
                DataSet setResult = new DataSet();
                DataTable tbLocal = null;
                DataRow dwLocal = null;
                bool flgExist = false;
                setResult = QueryWTSCHUSS(dwData);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    if (tbLocal.Rows.Count > 0)
                    {
                        flgExist = true;
                        dwLocal = tbLocal.Rows[0];
                    }
                    else
                    {
                        dwLocal = tbLocal.NewRow();
                    }
                }
                MapWTSCHUSS(ref dwLocal, dwData);

                if (!flgExist)
                {
                    InsertWTSCHUSS(dwLocal);
                }
                UpdateWTSCHUSS(dwLocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public void InsertWTSCHUSS(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            or1 = OracleDB.CreateCommand();
            switch (app.company)
            {
                case "JLT":
                    or1.CommandText = "INSERT INTO WTSCHUSS(WTSMANDANT, WTSSFLGNR, WTSSTAKZ, WTSBEZ, WTSCADKEY1, WTSCADKEY2, WTSMUSTER, WTSSRAPP, WTSGRAPP, WTSJAC, WTSPATRONE, WTSREGU, WTSANZFARB, WTSANZMFS, WTSGRDSCHT, WTSFOLGE, WTSSCHUSS01, WTSSCHUSS02, WTSAENDZL, WTSAENDBEN, WTSAENDDAT, WTSNEUBEN, WTSNEUDAT, WTSFAKTOR, WTSFILLER)"
                       + " VALUES (:WTSMANDANT, :WTSSFLGNR, ' ', '                    ', '             ', '      ', 'N', 0, 0, ' ', '               ', '          ', 0, '0', 0, "
                   + "'312031202020202000000000473220312020202020000000004720202020202020200000000020202020202020202000000000202020202020202020000000002020202020202020200000000020202020202020202000000000202020202020202020000000002020202020202020200000000020202020202020202000000000202020202020202020000000002020202020202020200000000020202020202020202000000000202020202020202020000000002020202020202020200000000020202020202020202000000000202020202020202020000000002020202020202020200000000020202020202020202000000000202020202020202020000000002020202020202020200000000020202020202020202000000000202020202020202020000000002020202020202020200000000020', '00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000', '00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',"
                   + "1, :WTSAENDBEN, :WTSAENDDAT,:WTSNEUBEN, :WTSNEUDAT, '0000', '                                                                                           ')";
                    or1.Parameters.Add("WTSMANDANT", OracleType.NVarChar).Value = dwData["WTSMANDANT"].ToString();
                    or1.Parameters.Add("WTSSFLGNR", OracleType.NVarChar).Value = dwData["WTSSFLGNR"].ToString();
                    or1.Parameters.Add("WTSAENDBEN", OracleType.NVarChar).Value = dwData["WTSAENDBEN"].ToString();
                    or1.Parameters.Add("WTSAENDDAT", OracleType.Number).Value = dwData["WTSAENDDAT"].ToString();
                    or1.Parameters.Add("WTSNEUBEN", OracleType.NVarChar).Value = dwData["WTSNEUBEN"].ToString();
                    or1.Parameters.Add("WTSNEUDAT", OracleType.Number).Value = dwData["WTSNEUDAT"].ToString();
                    break;
                case "YGR":
                case "TST":
                case "UBT":
                case "SXN":
                default:
                    or1.CommandText = "INSERT INTO WTSCHUSS ( WTSMANDANT,WTSSFLGNR,WTSSTAKZ,WTSBEZ,WTSCADKEY1,WTSCADKEY2,WTSMUSTER,WTSSRAPP,WTSGRAPP,WTSJAC,WTSPATRONE,WTSREGU,WTSANZFARB,WTSANZMFS,WTSGRDSCHT,WTSFOLGE,WTSSCHUSS01,WTSSCHUSS02,WTSFAKTOR,WTSFILLER,WTSAENDZL,WTSAENDBEN,WTSAENDDAT,WTSNEUBEN,WTSNEUDAT)"
                     + " VALUES (:WTSMANDANT:,:WTSSFLGNR:,:WTSSTAKZ:,:WTSBEZ:,:WTSCADKEY1:,:WTSCADKEY2:,:WTSMUSTER:,:WTSSRAPP:,:WTSGRAPP:,:WTSJAC:,:WTSPATRONE:,:WTSREGU:,:WTSANZFARB:,:WTSANZMFS:,:WTSGRDSCHT:,utl_raw.cast_to_raw(:FOLGE:),utl_raw.cast_to_raw(:SCHUSS01:),:utl_raw.cast_to_raw(:SCHUSS02:),:WTSFAKTOR:,:WTSFILLER:,:WTSAENDZL:,:WTSAENDBEN:,:WTSAENDDAT:,:WTSNEUBEN:,:WTSNEUDAT:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DeleteWTSCHUSS(string client, string art)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE WTSCHUSS WHERE WTSMANDANT=:WTSMANDANT AND WTSSFLGNR=':WTSSFLGNR";
            or1.Parameters.Add("WTSMANDANT", OracleType.NVarChar).Value = client;
            or1.Parameters.Add("WTSSFLGNR", OracleType.NVarChar).Value = art;
            Connect2Proxy(or1);
        }
        public DataSet QueryWTSCHUSS(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    SResult = QWTS00(dwData);
                    break;
                case 1:
                    SResult = QWTS01(dwData);
                    break;
            }
            return SResult;
        }
        public DataSet QWTS00(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM WTSCHUSS WHERE  ROWNUM<2 ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QWTS01(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT utl_raw.cast_to_varchar2(WTSFOLGE) AS FOLGE,utl_raw.cast_to_varchar2(WTSSCHUSS01) AS SCHUSS01,utl_raw.cast_to_varchar2(WTSSCHUSS02) AS SCHUSS02, P.* "
                + " FROM WTSCHUSS P WHERE WTSMANDANT=:WTSMANDANT: AND WTSSFLGNR=:WTSSFLGNR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
            //WTSFOLGE,WTSSCHUSS02
        }
        public DataSet QWTS02(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT DISTINCT WTSSFLGNR AS KEY,WTSSFLGNR AS DES FROM WTSCHUSS WHERE WTSMANDANT=:WTSMANDANT:  ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }

        public void UpdateWTSCHUSS(DataRow dwData, int option = 1)
        {

            if (option == 0)
            {
                switch (app.company)
                {
                    case "JLT":
                        UWTS01(dwData);
                        break;
                    case "YGR":
                    case "TST":
                    case "UBT":
                    default:
                        UWTS02(dwData);
                        break;
                }
            }

        }
        public void UWTS01(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE WTSCHUSS SET "
            + "WTSSTAKZ=:WTSSTAKZ,"
            + "WTSBEZ=:WTSBEZ,"
            + "WTSCADKEY1=:WTSCADKEY1,"
            + "WTSCADKEY2=:WTSCADKEY2,"
            + "WTSMUSTER=:WTSMUSTER,"
            + "WTSSRAPP=:WTSSRAPP,"
            + "WTSGRAPP=:WTSGRAPP,"
            + "WTSJAC=:WTSJAC,"
            + "WTSPATRONE=:WTSPATRONE,"
            + "WTSREGU=:WTSREGU,"
            + "WTSANZFARB=:WTSANZFARB,"
            + "WTSANZMFS=:WTSANZMFS,"
            + "WTSGRDSCHT=:WTSGRDSCHT,"
            + "WTSFOLGE=:WTSFOLGE,"
            + "WTSSCHUSS01=:WTSSCHUSS01,"
            + "WTSSCHUSS02=:WTSSCHUSS02,"
            + "WTSAENDZL=:WTSAENDZL,"
            + "WTSAENDBEN=:WTSAENDBEN,"
            + "WTSAENDDAT=:WTSAENDDAT,"
            + "WTSNEUBEN=:WTSNEUBEN,"
            + "WTSNEUDAT=:WTSNEUDAT,"
            + "WTSFAKTOR=:WTSFAKTOR,"
            + "WTSFILLER=:WTSFILLER "
            + " WHERE  WTSMANDANT=:WTSMANDANT AND WTSSFLGNR=:WTSSFLGNR";
            or1.Parameters.Add("WTSMANDANT", OracleType.NVarChar).Value = dwData["WTSMANDANT"].ToString();
            or1.Parameters.Add("WTSSFLGNR", OracleType.NVarChar).Value = dwData["WTSSFLGNR"].ToString();
            or1.Parameters.Add("WTSSTAKZ", OracleType.NVarChar).Value = dwData["WTSSTAKZ"].ToString();
            or1.Parameters.Add("WTSBEZ", OracleType.NVarChar).Value = dwData["WTSBEZ"].ToString();
            or1.Parameters.Add("WTSCADKEY1", OracleType.NVarChar).Value = dwData["WTSCADKEY1"].ToString();
            or1.Parameters.Add("WTSCADKEY2", OracleType.NVarChar).Value = dwData["WTSCADKEY2"].ToString();
            or1.Parameters.Add("WTSMUSTER", OracleType.NVarChar).Value = dwData["WTSMUSTER"].ToString();
            or1.Parameters.Add("WTSSRAPP", OracleType.NVarChar).Value = dwData["WTSSRAPP"].ToString();
            or1.Parameters.Add("WTSGRAPP", OracleType.NVarChar).Value = dwData["WTSGRAPP"].ToString();
            or1.Parameters.Add("WTSJAC", OracleType.NVarChar).Value = dwData["WTSJAC"].ToString();
            or1.Parameters.Add("WTSPATRONE", OracleType.NVarChar).Value = dwData["WTSPATRONE"].ToString();
            or1.Parameters.Add("WTSREGU", OracleType.NVarChar).Value = dwData["WTSREGU"].ToString();
            or1.Parameters.Add("WTSANZFARB", OracleType.NVarChar).Value = dwData["WTSANZFARB"].ToString();
            or1.Parameters.Add("WTSANZMFS", OracleType.NVarChar).Value = dwData["WTSANZMFS"].ToString();
            or1.Parameters.Add("WTSGRDSCHT", OracleType.NVarChar).Value = dwData["WTSGRDSCHT"].ToString();
            or1.Parameters.Add("WTSFOLGE", OracleType.NVarChar).Value = dwData["WTSFOLGE"].ToString();
            or1.Parameters.Add("WTSSCHUSS01", OracleType.NVarChar).Value = dwData["WTSSCHUSS01"].ToString();
            or1.Parameters.Add("WTSSCHUSS02", OracleType.NVarChar).Value = dwData["WTSSCHUSS02"].ToString();
            or1.Parameters.Add("WTSAENDZL", OracleType.NVarChar).Value = dwData["WTSAENDZL"].ToString();
            or1.Parameters.Add("WTSAENDBEN", OracleType.NVarChar).Value = dwData["WTSAENDBEN"].ToString();
            or1.Parameters.Add("WTSAENDDAT", OracleType.NVarChar).Value = dwData["WTSAENDDAT"].ToString();
            or1.Parameters.Add("WTSNEUBEN", OracleType.NVarChar).Value = dwData["WTSNEUBEN"].ToString();
            or1.Parameters.Add("WTSNEUDAT", OracleType.NVarChar).Value = dwData["WTSNEUDAT"].ToString();
            or1.Parameters.Add("WTSFAKTOR", OracleType.NVarChar).Value = dwData["WTSFAKTOR"].ToString();
            or1.Parameters.Add("WTSFILLER", OracleType.NVarChar).Value = dwData["WTSFILLER"].ToString();
            Connect2Proxy(or1);

        }
        public void UWTS02(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE WTSCHUSS SET WTSMANDANT=:WTSMANDANT:,WTSSFLGNR=:WTSSFLGNR:,WTSSTAKZ=:WTSSTAKZ:,WTSBEZ=:WTSBEZ:,WTSCADKEY1=:WTSCADKEY1:,WTSCADKEY2=:WTSCADKEY2:,WTSMUSTER=:WTSMUSTER:,WTSSRAPP=:WTSSRAPP:,WTSGRAPP=:WTSGRAPP:,WTSJAC=:WTSJAC:,WTSPATRONE=:WTSPATRONE:,WTSREGU=:WTSREGU:,WTSANZFARB=:WTSANZFARB:,WTSANZMFS=:WTSANZMFS:,WTSGRDSCHT=:WTSGRDSCHT:,"
                   + " WTSFOLGE=utl_raw.cast_to_raw(:FOLGE:),WTSSCHUSS01=utl_raw.cast_to_raw(:SCHUSS01:),WTSSCHUSS02=utl_raw.cast_to_raw(:SCHUSS02:),WTSFAKTOR=:WTSFAKTOR:,WTSFILLER=:WTSFILLER:,WTSAENDZL=:WTSAENDZL:,WTSAENDBEN=:WTSAENDBEN:,WTSAENDDAT=:WTSAENDDAT:,WTSNEUBEN=:WTSNEUBEN:,WTSNEUDAT=:WTSNEUDAT:"

                    + " WHERE  WTSMANDANT=:WTSMANDANT: AND WTSSFLGNR=:WTSSFLGNR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            Connect2Proxy(or1);

        }
    }
}